﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class BookingPassengerDAL
    {
        private const string SQL_SELECT_WS_BOOKING_PASSENGER = "select BKP_ID as PID, BKP_BOOKING_ID as BOOKING_ID,BKP_PASSENGER_NAME as PASSENGER_NAME,BKP_CHINESE_SPELL as CHINESE_SPELL,BKP_MOBILE_NUMBER as MOBILE_NUMBER,BKP_ID_TYPE_ID as ID_TYPE_ID,BKP_ID_NUMBER as ID_NUMBER,date_format(BKP_EXPIRY_DATE,'%Y-%m-%d') as EXPIRY_DATE,BKP_RCRE_STAFF_ID as RCRE_STAFF_ID,BKP_RCRE_TIME as RCRE_TIME,BKP_LCHG_STAFF_ID as LCHG_STAFF_ID,BKP_LCHG_TIME as LCHG_TIME,(select IDT_TYPE_NAME from WS_ID_TYPE where IDT_TYPE_ID=BKP_ID_TYPE_ID) as ID_TYPE_NAME,BKP_BIRTHDAY as BIRTHDAY,BKP_NATION_ID as NATION_ID,BKP_SEX as SEX from WS_BOOKING_PASSENGER where 1=1 ";
        private const string SQL_INSERT_WS_BOOKING_PASSENGER = "insert into WS_BOOKING_PASSENGER(BKP_ID,BKP_BOOKING_ID,BKP_PASSENGER_NAME,BKP_CHINESE_SPELL,BKP_MOBILE_NUMBER,BKP_ID_TYPE_ID,BKP_ID_NUMBER,BKP_EXPIRY_DATE,BKP_RCRE_STAFF_ID,BKP_RCRE_TIME,BKP_BIRTHDAY,BKP_NATION_ID,BKP_SEX) values(@PId,@BookingId,@PassengerName,@ChineseSpell,@MobileNumber,@IDTypeId,@IDNumber,@ExpiryDate,@LastStaffId,@LastTime,@Birthday,@NationId,@Sex)";
        private const string SQL_DELETE_WS_BOOKING_PASSENGER = "delete from WS_BOOKING_PASSENGER where 1=1 ";
        private const string SQL_COUNT_WS_BOOKING_PASSENGER = "select count(BKP_BOOKING_ID) from WS_BOOKING_PASSENGER where 1=1";

        private const string SQL_WHERE_ID = " and BKP_ID=@PId ";
        private const string SQL_WHERE_BOOKING_ID = " and BKP_BOOKING_ID=@BookingId ";
        private const string SQL_WHERE_ID_TYPE_ID = " and BKP_ID_TYPE_ID=@IDTypeId ";

        private const string PARM_ID = "@PId";
        private const string PARM_BOOKING_ID = "@BookingId";
        private const string PARM_PASSENGER_NAME = "@PassengerName";
        private const string PARM_CHINESE_SPELL = "@ChineseSpell";
        private const string PARM_MOBILE_NUMBER = "@MobileNumber";
        private const string PARM_ID_TYPE_ID = "@IDTypeId";
        private const string PARM_ID_NUMBER = "@IDNumber";
        private const string PARM_EXPIRY_DATE = "@ExpiryDate";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";
        private const string PARM_BIRTHDAY = "@Birthday";
        private const string PARM_NATION_ID = "@NationId";
        private const string PARM_SEX = "@Sex";

        Nullable<DateTime> NullDateTime = null;

        private static BookingPassengerDAL instance = new BookingPassengerDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static BookingPassengerDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(string bookingId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_BOOKING_PASSENGER);
            sbSQL.Append(SQL_WHERE_BOOKING_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = bookingId });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public List<BookingPassengerModel> Gets(string bookingId)
        {
            using (DataSet ds = this.GetDataSet(bookingId))
            {
                DataTable dt = ds.Tables[0];
                List<BookingPassengerModel> list = dt.AsEnumerable().Select(t => new BookingPassengerModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    BookingId = Convert.ToString(t.ItemArray[1]),
                    PassengerName = Convert.ToString(t.ItemArray[2]),
                    ChineseSpell = Convert.ToString(t.ItemArray[3]),
                    MobileNumber = Convert.ToString(t.ItemArray[4]),
                    IDTypeId = Convert.ToString(t.ItemArray[5]),
                    IDNumber = Convert.ToString(t.ItemArray[6]),
                    ExpiryDate = Convert.ToDateTime(t.ItemArray[7]),
                    CreatedStaffId = Convert.ToString(t.ItemArray[8]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[9]),
                    LastModifiedStaffId = t.IsNull(10) ? string.Empty : Convert.ToString(t.ItemArray[10]),
                    LastModifiedDate = t.IsNull(11) ? NullDateTime : Convert.ToDateTime(t.ItemArray[11]),
                    Birthday = t.IsNull(13) ? NullDateTime : Convert.ToDateTime(t.ItemArray[13]),
                    NationId = t.IsNull(14) ? string.Empty : Convert.ToString(t.ItemArray[14]),
                    Sex = t.IsNull(15) ? "M" : Convert.ToString(t.ItemArray[15])
                }).ToList<BookingPassengerModel>();

                return list;
            }
        }
        public int Insert(DbTransaction trans, BookingPassengerModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.BookingId });
            parms.Add(new MySqlParameter(PARM_PASSENGER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.PassengerName });
            parms.Add(new MySqlParameter(PARM_CHINESE_SPELL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ChineseSpell });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
            parms.Add(new MySqlParameter(PARM_ID_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDTypeId });
            parms.Add(new MySqlParameter(PARM_ID_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDNumber });
            parms.Add(new MySqlParameter(PARM_EXPIRY_DATE, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.ExpiryDate });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });
            parms.Add(new MySqlParameter(PARM_BIRTHDAY, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.Birthday });
            parms.Add(new MySqlParameter(PARM_NATION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.NationId });
            parms.Add(new MySqlParameter(PARM_SEX, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Sex });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_BOOKING_PASSENGER, parms.ToArray());
        }
        public int Delete(DbTransaction trans, string bookingId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_BOOKING_PASSENGER);
            sbSQL.Append(SQL_WHERE_BOOKING_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = bookingId });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool IDTypeUsing(string typeId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_BOOKING_PASSENGER);
            sbSQL.Append(SQL_WHERE_ID_TYPE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ID_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = typeId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
